package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import person.Guest;
import person.Person;
import controllers.ExceptionsTypes;
import event.Event;

public class GuestWrapper {
	
	public static ArrayList<Guest> getAllGuests(boolean closeConnection) throws Exception{
		ArrayList<Guest> guests = new ArrayList<Guest>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".guest ";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long idEvent = rs.getInt("id_event");
	        	long idGuest = rs.getInt("id_guest");
	        	Event e = EventWrapper.getEvent(idEvent, false);
	        	Person p = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	guests.add(new Guest(idGuest, p, e));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    return guests;
	}
	
	public static Guest getGuest(long idGuest, boolean closeConnection) throws Exception{
		Guest g = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".guest " +
				"WHERE id_guest = "+ idGuest;
	    
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	int idEvent = rs.getInt("id_event");
	        	Event e = EventWrapper.getEvent(idEvent, false);
	        	Person p = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	g = new Guest(idGuest, p, e);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    if(g == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return g;
	}
	
	public static Guest getGuest(String taxCode, boolean closeConnection) throws Exception{
		Guest g = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".guest " +
				"WHERE tax_code = "+ taxCode;
	    
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	int idEvent = rs.getInt("id_event");
	        	long idGuest = rs.getInt("id_guest");
	        	Event e = EventWrapper.getEvent(idEvent, false);
	        	Person p = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	g = new Guest(idGuest, p, e);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    if(g == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return g;
	}
	
	public static ArrayList<Guest> getEventGuests(Event event, boolean closeConnection) throws Exception{
		ArrayList<Guest> guests = new ArrayList<Guest>();
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".guest " +
				"WHERE id_event = "+ event.getId();
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long idGuest = rs.getInt("id_guest");
	        	Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	guests.add(new Guest(idGuest, pers, event));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    return guests;
	}
	
	public static void insertGuest(boolean closeConnection, Guest ... guests) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<guests.length; i++){
			Guest g= guests[i];
			try{
				PersonWrapper.getPerson(g.getTaxCode(), false);
			}catch(Exception ex){
				if(ex.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
					PersonWrapper.insertPerson(false, g);
				else
					throw ex;
			}
			String query = "INSERT INTO "+ DBWrapper.getDbname() +".guest (tax_code, id_event)" +
            "VALUES('" + g.getTaxCode() +"', " +  
					g.getEvent().getId() + ")";
	        int rows = stmt.executeUpdate(query);
	        if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        g.setId(getGuest(g.getTaxCode(), false).getId());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	public static void deleteGuest(boolean closeConnection, Guest guest) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".guest " +
	    		"WHERE id_guest = '"+guest.getId()+"'";
    	Statement stmt = DBWrapper.getConnection().createStatement();
        int rows = stmt.executeUpdate(query);
        if(rows>1)
        	throw new SQLException();
        else if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        PersonWrapper.deletePerson(false, guest); 
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}

}
